package com.trw.trp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;

import com.trw.trp.bean.ModelBean;
import com.trw.trp.bean.ModelDetail;
import com.trw.trp.bean.OrderBean;
import com.trw.trp.bean.OrderHistoryBean;
import com.trw.trp.bean.OrderScheduleInfo;
import com.trw.trp.bean.OrderTemplateBean;
import com.trw.trp.bean.SearchCriteriaBean;
import com.trw.trp.bean.UserBean;
import com.trw.trp.database.ConnectionPool;
import com.trw.trp.util.TRPConstants;
import com.trw.trp.util.TRPUtil;
import com.utuan.log.Log;
import com.utuan.log.LogFactory;

public class OrderDao extends AbstractDao {

	public enum ORDER_ACTION {
		Create, Edit, Approve, Reject, PutIntoSchedule, Complete, Transfer
	}

	private static Log log = LogFactory.getLog(OrderDao.class);

	private static final String SQL_INSERT_ORDER = "insert into orders(order_number,status,input_status,selected_models,created_by,created_date,is_template) values(?,?,?,?,?,?,?)";
	private static final String SQL_INSERT_ORDER_MODELS = "insert into order_models(order_id,model_id,model_order,client_unique_id,model_status) values(?,?,?,?,?)";
	private static final String SQL_INSERT_ORDER_FIELD_VALUE = "insert into order_field_value(order_model_id,field_id,field_value) values(?,?,?)";

	private static final String SQL_UPDATE_GENERAL_INFO = "update order_general_info set customer=?,project=?,product=?,plant=?,phase=?,project_number=?,test_purpose=?,report_mode=?,report_distribution=?,test_user_id=?,vv_user_id=?,um=?,jrt=?,cp=?,payer=? where general_info_id=?";
	private static final String SQL_INSERT_GENERAL_INFO = "insert into order_general_info (order_id,customer,project,product,plant,phase,project_number,test_purpose,report_mode,report_distribution,test_user_id,vv_user_id,um,jrt,cp,payer) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String SQL_SELECT_GENERAL_INFO = "select general_info_id,customer,project,product,plant,phase,project_number,test_purpose,report_mode,report_distribution,test_user_id,vv_user_id,um,jrt,cp,payer from trp.order_general_info where order_id=?";

	private static final String SQL_GET_ORDER = "select tmp.order_id,tmp.order_number,tmp.status,tmp.input_status,tmp.selected_models,tmp.created_date,tmp.created_by,tmp.model_id,tmp.model_order,tmp.client_unique_id,tmp.model_status,tmp.status_id,mf.field_id,ofv.field_value from(select o.order_id,o.order_number,o.status,o.input_status,o.selected_models,o.created_date,o.created_by,om.model_id,om.model_order,om.client_unique_id,om.model_status,wsm.status_id,om.order_model_id from trp.orders o, trp.order_models om,trp.models m,trp.wf_status_models wsm where o.order_number=? and o.is_template='N' and o.order_id=om.order_id and om.model_id=m.model_id and om.model_id=wsm.model_id order by om.model_order,wsm.status_id) tmp left join trp.model_fields mf on tmp.model_id=mf.model_id left join trp.order_field_value ofv on mf.field_id=ofv.field_id and tmp.order_model_id=ofv.order_model_id";
	private static final String SQL_GET_ORDER_BY_ID = "select tmp.order_id,tmp.order_number,tmp.status,tmp.input_status,tmp.selected_models,tmp.created_date,tmp.created_by,tmp.model_id,tmp.model_order,tmp.client_unique_id,tmp.model_status,tmp.status_id,mf.field_id,ofv.field_value from(select o.order_id,o.order_number,o.status,o.input_status,o.selected_models,o.created_date,o.created_by,om.model_id,om.model_order,om.client_unique_id,om.model_status,wsm.status_id,om.order_model_id from trp.orders o, trp.order_models om,trp.models m,trp.wf_status_models wsm where o.order_id=? and o.is_template=? and o.order_id=om.order_id and om.model_id=m.model_id and om.model_id=wsm.model_id order by om.model_order,wsm.status_id) tmp left join trp.model_fields mf on tmp.model_id=mf.model_id left join trp.order_field_value ofv on mf.field_id=ofv.field_id and tmp.order_model_id=ofv.order_model_id";
	private static final String SQL_GET_ORDER2 = "select o.order_id,o.order_number,o.status,o.input_status,o.selected_models,o.created_date,o.created_by from trp.orders o where o.order_number=?";
	private static final String SQL_GET_ORDER_BY_ID2 = "select o.order_id,o.order_number,o.status,o.input_status,o.selected_models,o.created_date,o.created_by from trp.orders o where o.order_id=?";
	private static final String SQL_GET_ORDERS = "SELECT O.ORDER_ID,O.ORDER_NUMBER,OGI.PROJECT_NUMBER,OGI.PROJECT,OGI.PRODUCT,O.STATUS,O.INPUT_STATUS,O.CREATED_DATE FROM TRP.ORDERS O,TRP.ORDER_GENERAL_INFO OGI where O.ORDER_ID=OGI.ORDER_ID AND O.IS_TEMPLATE='N'";
	private static final String SQL_GET_ORDERS_BY_MULTI = "SELECT o.order_id,o.order_number,OG.PROJECT_NUMBER,OG.PROJECT,OG.PRODUCT,o.status,o.input_status,o.created_date FROM trp.orders o, trp.order_general_info og where o.order_id=og.order_id AND O.IS_TEMPLATE='N'";

	private static final String SQL_UPDATE_ORDER_STATUS = "update trp.orders set status=? where order_id=?";
	private static final String SQL_UPDATE_ORDER_INPUT_STATUS = "update trp.orders set input_status=? where order_id=?";
	private static final String SQL_UPDATE_ORDER = "update orders set input_status=?,selected_models=? where order_id=?";

	private static final String SQL_INSERT_SAMPLE_INFO = "INSERT INTO TRP.ORDER_SAMPLE_INFO(ORDER_ID,CLIENT_UNIQUE_ID,SECTION_ID,FIELD_ID,FIELD_VALUE,RECORD_ORDER) VALUES(?,?,?,?,?,?)";
	private static final String SQL_GET_SAMPLE_INFO = "SELECT OM.MODEL_ID,OM.CLIENT_UNIQUE_ID,OSI.SECTION_ID,OSI.FIELD_ID,OSI.FIELD_VALUE,OSI.RECORD_ORDER FROM TRP.ORDERS O,TRP.ORDER_MODELS OM,TRP.ORDER_SAMPLE_INFO OSI WHERE OM.MODEL_ID IN(SELECT WSM.MODEL_ID FROM TRP.WF_STATUS_MODELS WSM WHERE WSM.WORKFLOW_ID=1 AND WSM.STATUS_ID=1) AND OM.CLIENT_UNIQUE_ID=OSI.CLIENT_UNIQUE_ID AND O.ORDER_ID=OM.ORDER_ID AND O.ORDER_ID=? ORDER BY OM.ORDER_MODEL_ID,OSI.SECTION_ID,OSI.RECORD_ORDER";

	private static final String SQL_INSERT_MATRIX_INFO = "INSERT INTO TRP.ORDER_MATRIX_INFO(ORDER_ID,CLIENT_UNIQUE_ID,FIELD_ID,FIELD_VALUE,RECORD_ORDER) VALUES(?,?,?,?,?)";
	private static final String SQL_GET_MATRIX_INFO = "SELECT OM.MODEL_ID,OMI.CLIENT_UNIQUE_ID,FIELD_ID,FIELD_VALUE,RECORD_ORDER FROM TRP.ORDER_MATRIX_INFO OMI,TRP.ORDER_MODELS OM WHERE OMI.CLIENT_UNIQUE_ID=OM.CLIENT_UNIQUE_ID AND OMI.ORDER_ID=? ORDER BY OMI.CLIENT_UNIQUE_ID,OMI.RECORD_ORDER";

	private static final String SQL_INSERT_ORDER_HISTORY = "insert into trp.order_history(order_id,order_action,change_user,change_date,comment) values(?,?,?,?,?)";
	private static final String SQL_GET_ORDER_HISTORY = "select oh.order_id,oh.order_action,u.name,oh.change_date,oh.comment from trp.order_history oh,trp.users u where oh.change_user=u.userid and oh.order_id=? order by change_date";

	private static final String SQL_GET_AUTO_NUMBER = "select auto_number from trp.auto_numbers where year=? and name=?";
	private static final String SQL_INSERT_AUTO_NUMBER = "insert into trp.auto_numbers(auto_number,year,name) values(?,?,?)";
	private static final String SQL_UPDATE_AUTO_NUMBER = "update trp.auto_numbers set auto_number=auto_number+1 where year=? and name=?";

	private static final String SQL_GET_SCHEDULE_INFO = "SELECT CLIENT_UNIQUE_ID,SAMPLE,DURATION,FIXTURE,OUT_SOURCING,TEST_LOCATION,START_TIME,END_TIME,STATUS FROM TRP.ORDER_SCHEDULE_INFO WHERE ORDER_ID=?";
	private static final String SQL_NEW_SCHEDULE_INFO = "INSERT INTO TRP.ORDER_SCHEDULE_INFO(ORDER_ID,CLIENT_UNIQUE_ID,STATUS) VALUES (?,?,?)";

	private static final String SQL_GET_SCHEDULE_DATA = "SELECT O.ORDER_NUMBER,M.MODEL_NAME,OSI.CONDITION,OSI.DURATION,OSI.START_TIME,OSI.END_TIME FROM TRP.ORDER_SCHEDULE_INFO OSI,TRP.ORDERS O,TRP.ORDER_MODELS OM,TRP.MODELS M WHERE OM.CLIENT_UNIQUE_ID=OSI.CLIENT_UNIQUE_ID AND OM.MODEL_ID=M.MODEL_ID AND OSI.CLIENT_UNIQUE_ID=OM.CLIENT_UNIQUE_ID AND O.ORDER_ID=OM.ORDER_ID AND OSI.ORDER_ID=O.ORDER_ID AND OSI.START_TIME>=? AND OSI.END_TIME<?";

	private static final String SQL_UPDATE_SCHEDULE_TIMESLOT = "UPDATE TRP.ORDER_SCHEDULE_INFO SET START_TIME=?,END_TIME=? WHERE CLIENT_UNIQUE_ID=? AND ORDER_ID=?";
	private static final String SQL_UPDATE_SCHEDULE_STATUS = "UPDATE TRP.ORDER_SCHEDULE_INFO SET STATUS=? WHERE CLIENT_UNIQUE_ID=? AND ORDER_ID=?";
	private static final String SQL_CHECK_SDNUMBER_EXIST = "SELECT SD_NUMBER FROM TRP.ORDER_SCHEDULE_INFO WHERE CLIENT_UNIQUE_ID=? AND ORDER_ID=?";
	private static final String SQL_UPDATE_SCHEDULE_SAMPLE_FIXTURE = "UPDATE TRP.ORDER_SCHEDULE_INFO SET SAMPLE=?,FIXTURE=? WHERE CLIENT_UNIQUE_ID=? AND ORDER_ID=?";
	private static final String SQL_UPDATE_SCHEDULE_INFO = "UPDATE TRP.ORDER_SCHEDULE_INFO SET DURATION=?,FIXTURE=?,OUT_SOURCING=?,TEST_LOCATION=? WHERE CLIENT_UNIQUE_ID=? AND ORDER_ID=?";

	private static final String SQL_COMPLTE_ORDERS = "UPDATE TRP.ORDERS OO SET OO.STATUS=13 WHERE OO.ORDER_ID IN("
			+ "SELECT TEMP1.ORDER_ID"
			+ " FROM (SELECT O.ORDER_ID,COUNT(OM.CLIENT_UNIQUE_ID) COUNT FROM TRP.ORDER_MODELS OM, TRP.ORDERS O WHERE O.ORDER_ID=OM.ORDER_ID AND O.STATUS=10 GROUP BY O.ORDER_ID) TEMP1,"
			+ "(SELECT OSI.ORDER_ID,COUNT(OSI.CLIENT_UNIQUE_ID) COUNT FROM TRP.ORDER_SCHEDULE_INFO OSI WHERE NOW()>=OSI.END_TIME GROUP BY OSI.ORDER_ID) TEMP2"
			+ " WHERE TEMP1.ORDER_ID=TEMP2.ORDER_ID AND TEMP1.COUNT=TEMP2.COUNT)";

	private static final String SQL_DELETE_ORDER_HISTORY = "DELETE FROM TRP.ORDER_HISTORY WHERE ORDER_ID=?";
	private static final String SQL_DELETE_ORDER_FIELD_VALUES = "DELETE FROM TRP.ORDER_FIELD_VALUE WHERE ORDER_MODEL_ID IN(SELECT OM.ORDER_MODEL_ID FROM TRP.ORDER_MODELS OM WHERE OM.ORDER_ID=?)";
	private static final String SQL_DELETE_ORDER_MODELS = "DELETE FROM TRP.ORDER_MODELS WHERE ORDER_ID=?";
	private static final String SQL_DELETE_GENERAL_INFO = "DELETE FROM TRP.ORDER_GENERAL_INFO WHERE ORDER_ID=?";
	private static final String SQL_DELETE_SAMPLE_INFO = "DELETE FROM TRP.ORDER_SAMPLE_INFO WHERE ORDER_ID=?";
	private static final String SQL_DELETE_MATRIX_INFO = "DELETE FROM TRP.ORDER_MATRIX_INFO WHERE ORDER_ID=?";
	private static final String SQL_DELETE_SCHEDULE_INFO = "DELETE FROM TRP.ORDER_SCHEDULE_INFO WHERE ORDER_ID=?";
	private static final String SQL_DELETE_SCHEDULE_INFO2 = "DELETE FROM TRP.ORDER_SCHEDULE_INFO WHERE ORDER_ID=? AND CLIENT_UNIQUE_ID=?";
	private static final String SQL_DELETE_ORDER = "DELETE FROM TRP.ORDERS WHERE ORDER_ID=?";
	private static final String SQL_DELETE_ORDER_TEMPLATE = "DELETE FROM TRP.ORDER_TEMPLATES WHERE ORDER_ID=?";
	private static final String SQL_DELETE_REF_USER = "DELETE FROM TRP.ORDER_REF_USER WHERE ORDER_ID=?";

	private static final String SQL_SAVE_ORDER_TEMPLATE = "INSERT INTO TRP.ORDER_TEMPLATES(TEMPLATE_NAME,IS_PRIVATE,ORDER_ID,CREATE_USER) VALUES(?,?,?,?)";
	private static final String SQL_GET_ORDER_TEMPLATE = "SELECT ORDER_TEMPLATE_ID FROM TRP.ORDER_TEMPLATES WHERE (TEMPLATE_NAME=? AND IS_PRIVATE='N') OR (TEMPLATE_NAME=? AND IS_PRIVATE='Y' AND CREATE_USER=?)";
	private static final String SQL_GET_ORDER_TEMPLATES = "SELECT ORDER_TEMPLATE_ID,ORDER_ID,TEMPLATE_NAME,IS_PRIVATE,CREATE_USER FROM TRP.ORDER_TEMPLATES WHERE IS_PRIVATE='N' OR CREATE_USER=? ORDER BY TEMPLATE_NAME";
	private static final String SQL_UPDATE_ORDER_TEMPLATE = "UPDATE TRP.ORDER_TEMPLATES SET TEMPLATE_NAME=?,IS_PRIVATE=? WHERE ORDER_TEMPLATE_ID=?";

	private static final String SQL_TRANSFER_ORDER = "UPDATE TRP.ORDER_GENERAL_INFO SET TEST_USER_ID=? WHERE ORDER_ID=?";
	private static final String SQL_ADD_REF_USER = "INSERT INTO TRP.ORDER_REF_USER(ORDER_ID,USER_ID) VALUES(?,?)";

	private ModelDao modelDao = DaoFactory.getModelDao();
	private UserDao userDao = DaoFactory.getUserDao();

	OrderDao() {
	}

	public long saveOrder(OrderBean order, int status) {
		return saveOrder(order, status, false);
	}

	public long saveOrder(OrderBean order, int status, boolean isTemplate) {
		log.enter("saveOrder", order, status);
		if (status == 2) {
			saveSampleInfo(order);
			return order.getOrderId();
		} else if (status == 3) {
			saveMatrixInfo(order);
			return order.getOrderId();
		} else if (status == 4) {
			saveTestPartInfo(order);
			return order.getOrderId();
		}
		long orderId = order.getOrderId();
		UserBean user = order.getUser();
		List<ModelBean> modelList = order.getModelMap() == null ? null : order
				.getModelMap().get(status);
		List<ModelDetail> detailList = order.getDetailMap() == null ? null
				: order.getDetailMap().get(status);
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		PreparedStatement ps4 = null;
		PreparedStatement ps5 = null;
		PreparedStatement ps6 = null;
		PreparedStatement ps7 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return orderId;
			}
			int inputStatus = status < order.getInputStatus() ? order
					.getInputStatus() : status;
			if (orderId == -1) {
				ps = conn.prepareStatement(SQL_INSERT_ORDER,
						PreparedStatement.RETURN_GENERATED_KEYS);
				ps
						.setString(
								1,
								order.getOrderNumber() == null ? getAutoNumber(TRPConstants.AUTO_NUMBER_TRP)
										: order.getOrderNumber());
				ps.setInt(2, order.getStatus());
				ps.setInt(3, inputStatus);
				ps.setString(4, order.getSelectedModels());
				ps.setLong(5, user.getUserId());
				ps.setTimestamp(6, new java.sql.Timestamp(System
						.currentTimeMillis()));
				ps.setString(7, isTemplate ? "Y" : "N");
				ps.execute();
				rs = ps.getGeneratedKeys();
				if (rs.next()) {
					orderId = rs.getLong(1);
					order.setOrderId(orderId);
				}
				createOrderHistory(conn, ORDER_ACTION.Create.toString(), order
						.getOrderId(), order.getUser().getUserId(), null);
			} else {
				ps = conn.prepareStatement(SQL_UPDATE_ORDER);
				ps.setInt(1, inputStatus);
				ps.setString(2, order.getSelectedModels());
				ps.setLong(3, orderId);
				ps.execute();
				createOrderHistory(conn, ORDER_ACTION.Edit.toString(), order
						.getOrderId(), order.getUser().getUserId(), null);
			}

			// insert/update generalinfo
			OrderBean.GeneralInfo generalInfo = order.getGeneralInfo();
			int i = 1;
			if (generalInfo.getId() > -1) {
				ps2 = conn.prepareStatement(SQL_UPDATE_GENERAL_INFO);
				ps2.setString(i++, generalInfo.getCustomer());
				ps2.setString(i++, generalInfo.getProject());
				ps2.setString(i++, generalInfo.getProduct());
				ps2.setString(i++, generalInfo.getPlant());
				ps2.setString(i++, generalInfo.getPhase());
				ps2.setString(i++, generalInfo.getProjectNumber());
				ps2.setString(i++, generalInfo.getTestPurpose());
				ps2.setString(i++, generalInfo.getReportMode());
				ps2.setString(i++, generalInfo.getReportDistribution());
				ps2.setLong(i++, generalInfo.getTestUser().getUserId());
				ps2.setLong(i++, generalInfo.getVvUser().getUserId());
				ps2.setString(i++, generalInfo.getUm());
				ps2.setString(i++, generalInfo.getJrt());
				ps2.setString(i++, generalInfo.getCp());
				ps2.setString(i++, generalInfo.getPayer());
				ps2.setLong(i++, generalInfo.getId());
			} else {
				ps7 = conn.prepareStatement(SQL_DELETE_GENERAL_INFO);
				ps7.setLong(1, orderId);
				ps7.execute();
				ps2 = conn.prepareStatement(SQL_INSERT_GENERAL_INFO);
				ps2.setLong(i++, orderId);
				ps2.setString(i++, generalInfo.getCustomer());
				ps2.setString(i++, generalInfo.getProject());
				ps2.setString(i++, generalInfo.getProduct());
				ps2.setString(i++, generalInfo.getPlant());
				ps2.setString(i++, generalInfo.getPhase());
				ps2.setString(i++, generalInfo.getProjectNumber());
				ps2.setString(i++, generalInfo.getTestPurpose());
				ps2.setString(i++, generalInfo.getReportMode());
				ps2.setString(i++, generalInfo.getReportDistribution());
				if (generalInfo.getTestUser() != null) {
					ps2.setLong(i++, generalInfo.getTestUser().getUserId());
				} else {
					ps2.setLong(i++, -1);
				}
				if (generalInfo.getVvUser() != null) {
					ps2.setLong(i++, generalInfo.getVvUser().getUserId());
				} else {
					ps2.setLong(i++, -1);
				}
				ps2.setString(i++, generalInfo.getUm());
				ps2.setString(i++, generalInfo.getJrt());
				ps2.setString(i++, generalInfo.getCp());
				ps2.setString(i++, generalInfo.getPayer());
			}
			ps2.execute();

			// delete order_field_value and order_models
			ps3 = conn.prepareStatement(SQL_DELETE_ORDER_FIELD_VALUES);
			ps4 = conn.prepareStatement(SQL_DELETE_ORDER_MODELS);
			ps3.setLong(1, orderId);
			ps3.execute();
			ps4.setLong(1, orderId);
			ps4.execute();

			// insert new order_models and order_field_value
			List<Long> orderModelIdList = new ArrayList<Long>();
			if (modelList != null) {
				int modelOrder = 0;
				ps5 = conn.prepareStatement(SQL_INSERT_ORDER_MODELS,
						PreparedStatement.RETURN_GENERATED_KEYS);
				for (i = 0; i < modelList.size(); i++) {
					ModelBean model = modelList.get(i);
					long orderModelId = -1;
					ps5.setLong(1, orderId);
					ps5.setLong(2, model.getModelId());
					ps5.setInt(3, modelOrder++);
					ps5.setString(4, model.getClientUniqueId());
					ps5.setInt(5, model.getStatus());
					ps5.execute();
					rs2 = ps5.getGeneratedKeys();
					if (rs2.next()) {
						orderModelId = rs2.getLong(1);
					}
					orderModelIdList.add(orderModelId);
				}
			}
			if (detailList != null) {
				int idx = 0;
				ps6 = conn.prepareStatement(SQL_INSERT_ORDER_FIELD_VALUE);
				for (ModelDetail detail : detailList) {
					if (detail == null || detail.getSectionList() == null) {
						idx++;
						continue;
					}
					for (ModelDetail.ModelSection section : detail
							.getSectionList()) {
						for (ModelDetail.Field field : section.getFieldList()) {
							ps6.setLong(1, orderModelIdList.get(idx));
							ps6.setLong(2, field.getFieldId());
							ps6.setString(3, field.getSelectedValue());
							ps6.execute();
						}
					}
					idx++;
				}
			}
			// refresh schedule info
			refreshScheduleInfo(order, conn);
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			close(null, rs2);
			close(conn, rs, ps, ps2, ps3, ps4, ps5, ps6, ps7);
		}
		log.exit("saveOrder");
		return orderId;
	}

	public OrderBean getOrder(String orderNumber) {
		log.enter("getOrder", orderNumber);
		OrderBean order = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		PreparedStatement ps2 = null;
		ResultSet rs2 = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return order;
			}
			ps = conn.prepareStatement(SQL_GET_ORDER);
			ps.setString(1, orderNumber);
			rs = ps.executeQuery();
			order = new OrderBean();
			Map<Integer, List<ModelBean>> modelMap = new HashMap<Integer, List<ModelBean>>();
			Map<Integer, List<ModelDetail>> detailMap = new HashMap<Integer, List<ModelDetail>>();
			order.setModelMap(modelMap);
			order.setDetailMap(detailMap);
			long orderId = -1;
			String oNumber = null;
			int orderStatus = -1;
			int orderInputStatus = -1;
			String selectedModels;
			java.sql.Timestamp createDate;
			long userId = -1;
			long modelId;
			int modelOrder;
			String clientUniqueId;
			int modelStatus;
			long orgModelOrder = -1;
			ModelDetail detail = null;
			int wfStatus = -1;
			while (rs.next()) {
				int i = 1;
				orderId = rs.getLong(i++);
				oNumber = rs.getString(i++);
				orderStatus = rs.getInt(i++);
				orderInputStatus = rs.getInt(i++);
				selectedModels = rs.getString(i++);
				createDate = rs.getTimestamp(i++);
				userId = rs.getLong(i++);
				modelId = rs.getLong(i++);
				modelOrder = rs.getInt(i++);
				clientUniqueId = rs.getString(i++);
				modelStatus = rs.getInt(i++);
				wfStatus = rs.getInt(i++);
				if (modelOrder != orgModelOrder) {
					if (order.getUser() == null) {
						order.setOrderId(orderId);
						order.setOrderNumber(oNumber);
						UserBean user = userDao.getUser(userId);
						order.setUser(user);
						order.setStatus(orderStatus);
						order.setInputStatus(orderInputStatus);
						order.setSelectedModels(selectedModels);
						order.setCreatedDate(new java.util.Date(createDate
								.getTime()));
					}

					ModelBean model = modelDao.getModelById(modelId);
					model.setClientUniqueId(clientUniqueId);
					model.setStatus(modelStatus);
					List<ModelBean> modelList = modelMap.get(wfStatus);
					if (modelList == null) {
						modelList = new ArrayList<ModelBean>();
						modelMap.put(wfStatus, modelList);
					}
					modelList.add(model);
					detail = modelDao.getModelDetail(modelId);
					List<ModelDetail> detailList = detailMap.get(wfStatus);
					if (detailList == null) {
						detailList = new ArrayList<ModelDetail>();
						detailMap.put(wfStatus, detailList);
					}
					detailList.add(detail);
					orgModelOrder = modelOrder;
				}
				long fieldId = rs.getLong(i++);
				String fieldValue = rs.getString(i++);

				if (detail != null) {
					for (ModelDetail.ModelSection section : detail
							.getSectionList()) {
						for (ModelDetail.Field field : section.getFieldList()) {
							if (fieldId == field.getFieldId()) {
								field.setSelectedValue(fieldValue);
								break;
							}
						}
					}
				}

			}
			if (order.getOrderNumber() == null) {
				ps2 = conn.prepareStatement(SQL_GET_ORDER2);
				ps2.setString(1, orderNumber);
				rs2 = ps2.executeQuery();
				if (rs2.next()) {
					int i = 1;
					order.setOrderId(rs2.getLong(i++));
					order.setOrderNumber(rs2.getString(i++));
					order.setStatus(rs2.getInt(i++));
					order.setInputStatus(rs2.getInt(i++));
					order.setSelectedModels(rs2.getString(i++));
					order.setCreatedDate(new java.util.Date(rs2.getTimestamp(
							i++).getTime()));
					UserBean user = userDao.getUser(rs2.getLong(i++));
					order.setUser(user);
				}
			}
			order.setGeneralInfo(getGeneralInfo(conn, order.getOrderId()));
			List<OrderBean.SampleInfo> sampleInfo = getSampleInfoList(conn,
					order);
			if (sampleInfo != null && sampleInfo.size() > 0) {
				order.setSampleInfoList(sampleInfo);
			}
			List<OrderBean.MatrixInfo> matrixInfo = getMatrixeInfoList(conn,
					order);
			if (matrixInfo != null && matrixInfo.size() > 0) {
				order.setMatrixInfoList(matrixInfo);
			}
			order.setScheduleInfoMap(getScheduleInfoMap(conn, order));
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs2);
			close(conn, rs, ps, ps2);
		}
		log.exit("getOrder", order);
		return order;
	}

	public OrderBean getOrder(long orderID) {
		log.enter("getOrder", orderID);
		OrderBean order = getOrder(orderID, null);
		log.exit("getOrder", order);
		return order;
	}

	public OrderBean getOrder(long orderID, boolean isTemplate) {
		log.enter("getOrder", orderID);
		OrderBean order = getOrder(orderID, null, isTemplate);
		log.exit("getOrder", order);
		return order;
	}

	private OrderBean getOrder(long orderID, Connection conn) {
		return getOrder(orderID, conn, false);
	}

	private OrderBean getOrder(long orderID, Connection conn, boolean isTemplate) {
		OrderBean order = null;
		boolean needReleaseConnection = false;
		try {
			if (conn == null) {
				conn = getConnection();
				needReleaseConnection = true;
			}
			if (conn == null) {
				log.debug("can't get db connection.");
				return order;
			}
			order = getModelInfo(conn, orderID, isTemplate);
			if (order != null) {
				order.setGeneralInfo(getGeneralInfo(conn, order.getOrderId()));
				List<OrderBean.SampleInfo> sampleInfo = getSampleInfoList(conn,
						order);
				if (sampleInfo != null && sampleInfo.size() > 0) {
					order.setSampleInfoList(sampleInfo);
				}
				List<OrderBean.MatrixInfo> matrixInfo = getMatrixeInfoList(
						conn, order);
				if (matrixInfo != null && matrixInfo.size() > 0) {
					order.setMatrixInfoList(matrixInfo);
				}
				order.setScheduleInfoMap(getScheduleInfoMap(conn, order));
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			if (needReleaseConnection) {
				close(conn, null);
			}
		}
		return order;
	}

	private OrderBean getModelInfo(Connection conn, long orderId,
			boolean isTemplate) {
		OrderBean order = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			ps = conn.prepareStatement(SQL_GET_ORDER_BY_ID);
			ps.setLong(1, orderId);
			ps.setString(2, isTemplate ? "Y" : "N");
			rs = ps.executeQuery();
			order = new OrderBean();
			Map<Integer, List<ModelBean>> modelMap = new HashMap<Integer, List<ModelBean>>();
			Map<Integer, List<ModelDetail>> detailMap = new HashMap<Integer, List<ModelDetail>>();
			order.setModelMap(modelMap);
			order.setDetailMap(detailMap);
			String oNumber = null;
			int orderStatus = -1;
			int orderInputStatus = -1;
			String selectedModels;
			java.sql.Timestamp createDate;
			long userId = -1;
			long modelId;
			int modelOrder;
			String clientUniqueId;
			int modelStatus;
			long orgModelOrder = -1;
			int orgModelStatus = -1;
			ModelDetail detail = null;
			int wfStatus = -1;
			while (rs.next()) {
				int i = 1;
				orderId = rs.getLong(i++);
				oNumber = rs.getString(i++);
				orderStatus = rs.getInt(i++);
				orderInputStatus = rs.getInt(i++);
				selectedModels = rs.getString(i++);
				createDate = rs.getTimestamp(i++);
				userId = rs.getLong(i++);
				modelId = rs.getLong(i++);
				modelOrder = rs.getInt(i++);
				clientUniqueId = rs.getString(i++);
				modelStatus = rs.getInt(i++);
				wfStatus = rs.getInt(i++);
				if (modelOrder != orgModelOrder || wfStatus != orgModelStatus) {
					if (order.getUser() == null) {
						order.setOrderId(orderId);
						order.setOrderNumber(oNumber);
						UserBean user = userDao.getUser(userId);
						order.setUser(user);
						order.setStatus(orderStatus);
						order.setInputStatus(orderInputStatus);
						order.setSelectedModels(selectedModels);
						order.setCreatedDate(new java.util.Date(createDate
								.getTime()));
					}

					ModelBean model = modelDao.getModelById(modelId);
					if (model == null) {
						continue;
					}
					model.setClientUniqueId(clientUniqueId);
					model.setStatus(modelStatus);
					List<ModelBean> modelList = modelMap.get(wfStatus);
					if (modelList == null) {
						modelList = new ArrayList<ModelBean>();
						modelMap.put(wfStatus, modelList);
					}
					modelList.add(model);
					detail = modelDao.getModelDetail(modelId);
					List<ModelDetail> detailList = detailMap.get(wfStatus);
					if (detailList == null) {
						detailList = new ArrayList<ModelDetail>();
						detailMap.put(wfStatus, detailList);
					}
					detailList.add(detail);
					orgModelOrder = modelOrder;
					orgModelStatus = wfStatus;
				}
				long fieldId = rs.getLong(i++);
				String fieldValue = rs.getString(i++);
				if (detail != null) {
					for (ModelDetail.ModelSection section : detail
							.getSectionList()) {
						for (ModelDetail.Field field : section.getFieldList()) {
							if (fieldId == field.getFieldId()) {
								field.setSelectedValue(fieldValue);
								break;
							}
						}
					}
				}
			}
			if (order.getOrderNumber() == null) {
				ps2 = conn.prepareStatement(SQL_GET_ORDER_BY_ID2);
				ps2.setLong(1, orderId);
				rs2 = ps2.executeQuery();
				if (rs2.next()) {
					int i = 1;
					order.setOrderId(rs2.getLong(i++));
					order.setOrderNumber(rs2.getString(i++));
					order.setStatus(rs2.getInt(i++));
					order.setInputStatus(rs2.getInt(i++));
					order.setSelectedModels(rs2.getString(i++));
					order.setCreatedDate(new java.util.Date(rs2.getTimestamp(
							i++).getTime()));
					UserBean user = userDao.getUser(rs2.getLong(i++));
					order.setUser(user);
				}
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs);
			close(null, rs, ps, ps2);
		}
		return order;
	}

	public void createScheduleInfo(List<OrderBean> orders, UserBean actor) {
		log.enter("createScheduleInfo", orders, actor);
		if (orders != null && !orders.isEmpty()) {
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				conn = ConnectionPool.getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return;
				}
				ps = conn.prepareStatement(SQL_UPDATE_SCHEDULE_INFO);
				for (OrderBean order : orders) {
					Map<String, OrderScheduleInfo> scheduleInfoMap = order
							.getScheduleInfoMap();
					OrderScheduleInfo scheduleInfo = null;
					if (scheduleInfoMap != null) {
						for (String clientUniqueId : scheduleInfoMap.keySet()) {
							boolean exist = checkScheduleInfoExist(order
									.getOrderId(), clientUniqueId, conn);
							if (!exist) {
								List<String> tempList = new ArrayList<String>(1);
								tempList.add(clientUniqueId);
								createScheduleInfo0(order.getOrderId(),
										tempList, conn);
							}
							scheduleInfo = scheduleInfoMap.get(clientUniqueId);
							String duration = null;
							String fixture = null;
							String outSourcing = null;
							String testLocation = null;
							if (scheduleInfo != null) {
								duration = scheduleInfo.getDuration();
								fixture = scheduleInfo.getFixture();
								outSourcing = scheduleInfo.getIsOutSourcing();
								testLocation = scheduleInfo.getTestLocation();
							}
							int i = 1;
							ps.setString(i++, duration);
							ps.setString(i++, fixture);
							ps.setString(i++, outSourcing);
							ps.setString(i++, testLocation);
							ps.setString(i++, clientUniqueId);
							ps.setLong(i++, order.getOrderId());
							ps.execute();
						}
					}
				}
				conn.commit();
			} catch (SQLException e) {
				log.warn(e);
				try {
					conn.rollback();
				} catch (SQLException e1) {
					log.warn(e1);
				}
			} finally {
				close(conn, null, ps);
			}
		}
		log.exit("createScheduleInfo");
	}

	private Map<String, OrderScheduleInfo> getScheduleInfoMap(Connection conn,
			OrderBean order) {
		Map<String, OrderScheduleInfo> scheduleInfoMap = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			scheduleInfoMap = new HashMap<String, OrderScheduleInfo>();
			ps = conn.prepareStatement(SQL_GET_SCHEDULE_INFO);
			ps.setLong(1, order.getOrderId());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String clientUniqueId = rs.getString(i++);
				String sample = rs.getString(i++);
				String duration = rs.getString(i++);
				String fixture = rs.getString(i++);
				String outSourcing = rs.getString(i++);
				String testLocation = rs.getString(i++);
				java.sql.Timestamp sTime = rs.getTimestamp(i++);
				Date startTime = sTime == null ? null : new java.util.Date(
						sTime.getTime());
				java.sql.Timestamp eTime = rs.getTimestamp(i++);
				Date endTime = eTime == null ? null : new java.util.Date(eTime
						.getTime());
				int status = rs.getInt(i++);
				OrderScheduleInfo scheduleInfo = new OrderScheduleInfo();
				scheduleInfo.setSample(sample);
				scheduleInfo.setFixture(fixture);
				scheduleInfo.setDuration(duration);
				scheduleInfo.setIsOutSourcing(outSourcing);
				scheduleInfo.setTestLocation(testLocation);
				scheduleInfo.setStartTime(startTime);
				scheduleInfo.setEndTime(endTime);
				scheduleInfo.setClientUniqueId(clientUniqueId);
				scheduleInfo.setStatus(status);
				scheduleInfoMap.put(clientUniqueId, scheduleInfo);
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs, ps);
		}
		return scheduleInfoMap;
	}

	private List<OrderBean.SampleInfo> getSampleInfoList(Connection conn,
			OrderBean order) {
		List<OrderBean.SampleInfo> sampleInfoList = new ArrayList<OrderBean.SampleInfo>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_GET_SAMPLE_INFO);
			ps.setLong(1, order.getOrderId());
			rs = ps.executeQuery();
			long modelId;
			String orgClientUniqueId = null;
			String clientUniqueId;
			long orgSectionId = -1;
			long sectionId;
			long fieldId;
			String fieldValue;
			int orgRecordOrder = -1;
			int recordOrder;
			OrderBean.SampleInfo sampleInfo = null;
			List<List<ModelDetail.Field>> fieldList = null;
			List<ModelDetail.Field> record = null;
			List<ModelDetail.Field> tempFieldList = null;
			while (rs.next()) {
				int i = 1;
				modelId = rs.getLong(i++);
				// hard code here
				if (modelId == 12) {
					modelId = 4;
				}
				clientUniqueId = rs.getString(i++);
				sectionId = rs.getLong(i++);
				fieldId = rs.getLong(i++);
				fieldValue = rs.getString(i++);
				recordOrder = rs.getInt(i++);
				if (orgClientUniqueId == null
						|| !orgClientUniqueId.equals(clientUniqueId)) {
					sampleInfo = new OrderBean.SampleInfo();
					sampleInfo.setClientUniqueId(clientUniqueId);
					sampleInfoList.add(sampleInfo);
					orgClientUniqueId = clientUniqueId;
					orgSectionId = -1;
					orgRecordOrder = -1;
				}
				if (orgSectionId != sectionId) {
					if (sampleInfo.getFieldListMap() == null) {
						sampleInfo
								.setFieldListMap(new HashMap<Long, List<List<ModelDetail.Field>>>());
					}
					fieldList = new ArrayList<List<ModelDetail.Field>>();
					sampleInfo.getFieldListMap().put(sectionId, fieldList);
					List<ModelDetail.ModelSection> sectionList = modelDao
							.getModelDetail(modelId).getSectionList();
					for (ModelDetail.ModelSection section : sectionList) {
						if (section.getId() == sectionId) {
							tempFieldList = section.getFieldList();
							break;
						}
					}
					orgSectionId = sectionId;
				}
				if (orgRecordOrder != recordOrder) {
					record = new ArrayList<ModelDetail.Field>();
					for (ModelDetail.Field field : tempFieldList) {
						try {
							record.add((ModelDetail.Field) field.clone());
						} catch (CloneNotSupportedException e) {
							log.warn(e);
						}
					}
					fieldList.add(record);
					orgRecordOrder = recordOrder;
				}
				for (ModelDetail.Field field : record) {
					if (field.getFieldId() == fieldId) {
						field.setSelectedValue(fieldValue);
						break;
					}
				}
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs, ps);
		}

		return sampleInfoList;
	}

	private List<OrderBean.MatrixInfo> getMatrixeInfoList(Connection conn,
			OrderBean order) {
		List<OrderBean.MatrixInfo> matrixInfoList = new ArrayList<OrderBean.MatrixInfo>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_GET_MATRIX_INFO);
			ps.setLong(1, order.getOrderId());
			rs = ps.executeQuery();
			long modelId;
			String orgClientUniqueId = null;
			String clientUniqueId;
			long fieldId;
			String fieldValue;
			int orgRecordOrder = -1;
			int recordOrder;
			OrderBean.MatrixInfo matrixInfo = null;
			List<List<ModelDetail.Field>> fieldList = null;
			List<ModelDetail.Field> record = null;
			ModelDetail detail = null;
			while (rs.next()) {
				int i = 1;
				modelId = rs.getLong(i++);
				clientUniqueId = rs.getString(i++);
				fieldId = rs.getLong(i++);
				fieldValue = rs.getString(i++);
				recordOrder = rs.getInt(i++);
				if (orgClientUniqueId == null
						|| !orgClientUniqueId.equals(clientUniqueId)) {
					matrixInfo = new OrderBean.MatrixInfo();
					matrixInfo.setClientUniqueId(clientUniqueId);
					fieldList = new ArrayList<List<ModelDetail.Field>>();
					matrixInfo.setFieldList(fieldList);
					matrixInfoList.add(matrixInfo);
					orgClientUniqueId = clientUniqueId;
					orgRecordOrder = -1;
					if (modelId == 12) {
						detail = modelDao.getModelDetail(29L);
					} else if (modelId == 20 || modelId == 21 || modelId == 22) {
						detail = modelDao.getModelDetail(30L);
					}
				}

				if (orgRecordOrder != recordOrder) {
					record = new ArrayList<ModelDetail.Field>();
					for (ModelDetail.Field field : detail.getSectionList().get(
							0).getFieldList()) {
						try {
							record.add((ModelDetail.Field) field.clone());
						} catch (CloneNotSupportedException e) {
							log.warn(e);
						}
					}
					fieldList.add(record);
					orgRecordOrder = recordOrder;
				}
				for (ModelDetail.Field field : record) {
					if (field.getFieldId() == fieldId) {
						field.setSelectedValue(fieldValue);
						break;
					}
				}
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs, ps);
		}

		return matrixInfoList;
	}

	private OrderBean.GeneralInfo getGeneralInfo(Connection conn, long orderId)
			throws SQLException {
		OrderBean.GeneralInfo g = null;
		PreparedStatement ps = conn.prepareStatement(SQL_SELECT_GENERAL_INFO);
		ps.setLong(1, orderId);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			int i = 1;
			g = new OrderBean.GeneralInfo();
			g.setId(rs.getLong(i++));
			g.setCustomer(rs.getString(i++));
			g.setProject(rs.getString(i++));
			g.setProduct(rs.getString(i++));
			g.setPlant(rs.getString(i++));
			g.setPhase(rs.getString(i++));
			g.setProjectNumber(rs.getString(i++));
			g.setTestPurpose(rs.getString(i++));
			g.setReportMode(rs.getString(i++));
			g.setReportDistribution(rs.getString(i++));
			UserBean testUser = userDao.getUser(rs.getLong(i++));
			g.setTestUser(testUser);
			UserBean vvUser = userDao.getUser(rs.getLong(i++));
			g.setVvUser(vvUser);
			g.setUm(rs.getString(i++));
			g.setJrt(rs.getString(i++));
			g.setCp(rs.getString(i++));
			g.setPayer(rs.getString(i++));
		}
		return g;
	}

	public List<OrderBean> getOrdersByMulti(SearchCriteriaBean criteria) {
		log.enter("getOrdersByMulti", criteria);
		List<OrderBean> orderList = new ArrayList<OrderBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder(SQL_GET_ORDERS_BY_MULTI);
		boolean appendCustomer = (criteria.getCustomer() != null && criteria
				.getCustomer().trim().length() > 0);
		boolean appendProject = (criteria.getProject() != null && criteria
				.getProject().trim().length() > 0);
		boolean appendProduct = (criteria.getProduct() != null && criteria
				.getProduct().trim().length() > 0);
		boolean appendProjNum = (criteria.getProjectNum() != null && criteria
				.getProjectNum().trim().length() > 0);

		if (appendCustomer) {
			sb.append(" and customer=? ");
		}
		if (appendProject) {
			sb.append(" and project=? ");
		}
		if (appendProduct) {
			sb.append(" and product=? ");
		}
		if (appendProjNum) {
			sb.append(" and order_number like '%?%'");
		}
		sb.append(" order by created_date desc");
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return orderList;
			}
			ps = conn.prepareStatement(sb.toString());
			int i = 1;
			if (appendCustomer) {
				ps.setString(i++, criteria.getCustomer());
			}
			if (appendProject) {
				ps.setString(i++, criteria.getProject());
			}
			if (appendProduct) {
				ps.setString(i++, criteria.getProduct());
			}
			if (appendProjNum) {
				ps.setString(i++, criteria.getProjectNum());
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				i = 1;
				OrderBean ob = new OrderBean();
				ob.setOrderId(rs.getLong(i++));
				ob.setOrderNumber(rs.getString(i++));
				ob.setPrjNumber(rs.getString(i++));
				ob.setPrjName(getPrjName(rs.getString(i++), rs.getString(i++)));
				int status = rs.getInt(i++);
				status = getOrderStatus(conn, ob, status);
				ob.setStatus(status);
				ob.setInputStatus(rs.getInt(i++));
				Date createdDate = rs.getTimestamp(i++);
				ob.setCreatedDate(new java.util.Date(createdDate.getTime()));
				orderList.add(ob);
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			close(conn, rs, ps);
		}
		return orderList;
	}

	public List<OrderBean> getOrders(SearchCriteriaBean criteria) {
		log.enter("getOrders", criteria);
		List<OrderBean> orderList = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			orderList = new ArrayList<OrderBean>();
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return orderList;
			}
			StringBuilder sb = new StringBuilder(SQL_GET_ORDERS);
			if (criteria.getStartTime() != null) {
				sb.append(" and created_date>? ");
			}
			if (criteria.getEndTime() != null) {
				sb.append(" and created_date<? ");
			}
			if (criteria.getOrderNumber() != null) {
				sb.append(" and order_number like ?");
			}
			if (criteria.getUserId() > -1) {
				sb.append(" and created_by=?");
			}
			if (criteria.getMaxStatus() > -999) {
				sb.append(" and o.status < ?+1");
			}
			if (criteria.getMinStatus() > -999) {
				sb.append(" and o.status > ?-1");
			}
			if (criteria.getTeUserId() > -1) {
				sb
						.append(
								" and (exists(select 1 from trp.order_general_info ogi where ogi.order_id=o.order_id and ogi.test_user_id=?)")
						.append(
								" or exists(select 1 from trp.order_ref_user oru where oru.order_id=o.order_id and oru.user_id=?))");
			}
			sb.append(" order by created_date desc");
			ps = conn.prepareStatement(sb.toString());
			int i = 1;
			if (criteria.getStartTime() != null) {
				ps.setTimestamp(i++, new java.sql.Timestamp(criteria
						.getStartTime().getTime()));
			}
			if (criteria.getEndTime() != null) {
				ps.setTimestamp(i++, new java.sql.Timestamp(criteria
						.getEndTime().getTime()));
			}
			if (criteria.getOrderNumber() != null) {
				String orderNumber = null;
				if (SearchCriteriaBean.SearchType.contains.equals(criteria
						.getSearchType())) {
					orderNumber = "%" + criteria.getOrderNumber() + "%";
				} else if (SearchCriteriaBean.SearchType.startWith
						.equals(criteria.getSearchType())) {
					orderNumber = criteria.getOrderNumber() + "%";
				} else if (SearchCriteriaBean.SearchType.equals.equals(criteria
						.getSearchType())) {
					orderNumber = criteria.getOrderNumber();
				} else {
					orderNumber = "%";
				}
				ps.setString(i++, orderNumber);
			}
			if (criteria.getUserId() > -1) {
				ps.setLong(i++, criteria.getUserId());
			}
			if (criteria.getMaxStatus() > -999) {
				ps.setInt(i++, criteria.getMaxStatus());
			}
			if (criteria.getMinStatus() > -999) {
				ps.setInt(i++, criteria.getMinStatus());
			}
			if (criteria.getTeUserId() > -1) {
				ps.setLong(i++, criteria.getTeUserId());
				ps.setLong(i++, criteria.getTeUserId());
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				i = 1;
				long orderId = rs.getLong(i++);
				String oNumber = rs.getString(i++);
				String projectNumber = rs.getString(i++);
				String project = rs.getString(i++);
				String product = rs.getString(i++);
				int status = rs.getInt(i++);
				int inputStatus = rs.getInt(i++);
				java.sql.Timestamp createdDate = rs.getTimestamp(i++);
				OrderBean order = new OrderBean();
				order.setOrderId(orderId);
				order.setOrderNumber(oNumber);
				order.setPrjNumber(projectNumber);
				order.setPrjName(getPrjName(project, product));
				order.setInputStatus(inputStatus);
				order.setCreatedDate(new java.util.Date(createdDate.getTime()));
				status = getOrderStatus(conn, order, status);
				order.setStatus(status);
				orderList.add(order);
			}
			if (criteria.isDetail()) {
				List<OrderBean> tempList = new ArrayList<OrderBean>();
				for (OrderBean odr : orderList) {
					tempList.add(getOrder(odr.getOrderId(), conn));
				}
				orderList = tempList;
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		log.exit("getOrders", orderList);
		return orderList;
	}

	private int getOrderStatus(Connection conn, OrderBean order, int status) {
		if (status == 2) {
			Map<String, OrderScheduleInfo> scheduleMap = getScheduleInfoMap(
					conn, order);
			if (scheduleMap != null && scheduleMap.size() > 0) {
				status = 11;
				for (OrderScheduleInfo scheduleInfo : scheduleMap.values()) {
					if (scheduleInfo.isCompleted()) {
						status = 12;
						break;
					}
				}
			}
		}
		return status;
	}

	public synchronized String getAutoNumber(String autoNumberName) {
		return getAutoNumber(autoNumberName, null);
	}

	public synchronized String getAutoNumber(String autoNumberName,
			Connection conn) {
		log.enter("getOrderNumber");
		String orderNumber = null;
		int orderId = 0;
		Calendar cal = new GregorianCalendar();
		cal.setTime(new java.util.Date());
		int year = cal.get(Calendar.YEAR);
		boolean releaseConn = false;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		try {
			if (conn == null) {
				conn = getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return orderNumber;
				}
				releaseConn = true;
			}
			ps = conn.prepareStatement(SQL_GET_AUTO_NUMBER);
			ps.setInt(1, year);
			ps.setString(2, autoNumberName);
			rs = ps.executeQuery();
			if (rs.next()) {
				orderId = rs.getInt(1);
				ps2 = conn.prepareStatement(SQL_UPDATE_AUTO_NUMBER);
				ps2.setInt(1, year);
				ps2.setString(2, autoNumberName);
				ps2.execute();
			} else {
				orderId = 1;
				ps2 = conn.prepareStatement(SQL_INSERT_AUTO_NUMBER);
				ps2.setInt(1, 2);
				ps2.setInt(2, year);
				ps2.setString(3, autoNumberName);
				ps2.execute();
			}
			if (releaseConn) {
				conn.commit();
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			if (releaseConn) {
				close(conn, rs, ps, ps2);
			} else {
				close(null, rs, ps, ps2);
			}
		}
		if (TRPConstants.AUTO_NUMBER_TRP.equals(autoNumberName)) {
			orderNumber = TRPUtil.generateOrderNumber(year, orderId);
		} else if (TRPConstants.AUTO_NUMBER_SD.equals(autoNumberName)) {
			orderNumber = TRPUtil.generateSDNumber(year, orderId);
		}
		log.exit("getOrderNumber", orderNumber);
		return orderNumber;
	}

	public void changeOrderStatus(OrderBean order, int status, String action,
			UserBean actor, String comment) {
		log.enter("changeOrderStatus", order, status, action, actor, comment);
		changeOrderStatus(null, order, status, action, actor, comment);
		log.exit("changeOrderStatus");
	}

	private void changeOrderStatus(Connection conn, OrderBean order,
			int status, String action, UserBean actor, String comment) {
		PreparedStatement ps = null;
		boolean releaseConnection = false;
		try {
			if (conn == null) {
				conn = getConnection();
				releaseConnection = true;
			}
			if (conn == null) {
				log.debug("can't get db connection.");
				return;
			}
			ps = conn.prepareStatement(SQL_UPDATE_ORDER_STATUS);
			ps.setInt(1, status);
			ps.setLong(2, order.getOrderId());
			ps.execute();
			if (action != null) {
				createOrderHistory(conn, action, order.getOrderId(), actor
						.getUserId(), comment);
			}
			/*
			 * // generate schedule info if pending green status if (status ==
			 * 3) { createScheduleInfo(order, conn); }
			 */
			if (releaseConnection) {
				conn.commit();
			}
			order.setStatus(status);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			if (releaseConnection) {
				close(conn, null, ps);
			} else {
				close(null, null, ps);
			}
		}
	}

	private boolean checkScheduleInfoExist(long orderId, String clientUniqueId,
			Connection conn) {
		boolean exist = false;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_CHECK_SDNUMBER_EXIST);
			ps.setString(1, clientUniqueId);
			ps.setLong(2, orderId);
			rs = ps.executeQuery();
			if (rs.next()) {
				exist = true;
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, rs, ps);
		}
		return exist;
	}

	private void createScheduleInfo0(long orderId, Collection<String> cuidList,
			Connection conn) {
		PreparedStatement ps2 = null;
		try {
			ps2 = conn.prepareStatement(SQL_NEW_SCHEDULE_INFO);
			for (String cuid : cuidList) {
				ps2.setLong(1, orderId);
				ps2.setString(2, cuid);
				ps2.setInt(3, -999);
				ps2.executeUpdate();
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(null, null, ps2);
		}
	}

	private void createOrderHistory(Connection conn, String action,
			long orderId, long actionUserId, String comment) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(SQL_INSERT_ORDER_HISTORY);
			ps.setLong(1, orderId);
			ps.setString(2, action);
			ps.setLong(3, actionUserId);
			ps.setTimestamp(4, new java.sql.Timestamp(System
					.currentTimeMillis()));
			ps.setString(5, comment);
			ps.execute();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(null, null, ps);
		}
	}

	public List<OrderHistoryBean> getHistory(long orderId) {
		log.enter("getHistory", orderId);
		List<OrderHistoryBean> ret = new ArrayList<OrderHistoryBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return ret;
			}
			ps = conn.prepareStatement(SQL_GET_ORDER_HISTORY);
			ps.setLong(1, orderId);
			rs = ps.executeQuery();
			String action;
			String changeUser;
			Date changeDate;
			String comment;
			while (rs.next()) {
				action = rs.getString(2);
				changeUser = rs.getString(3);
				changeDate = new Date(rs.getTimestamp(4).getTime());
				comment = rs.getString(5);
				OrderHistoryBean history = new OrderHistoryBean();
				history.setOrderId(orderId);
				history.setAction(action);
				history.setChangeUser(changeUser);
				history.setChangeDate(TRPConstants.DATE_FORMAT2
						.format(changeDate));
				history.setComment(comment);
				ret.add(history);
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		log.exit("getHistory", ret);
		return ret;
	}

	public void saveSampleInfo(OrderBean order) {
		log.enter("saveSampleInfo", order);
		if (order == null) {
			return;
		}
		List<OrderBean.SampleInfo> sampleInfoList = order.getSampleInfoList();
		Connection conn = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs2 = null;
		try {
			if (sampleInfoList != null) {
				conn = getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return;
				}
				ps3 = conn.prepareStatement(SQL_INSERT_SAMPLE_INFO);
				ps2 = conn.prepareStatement(SQL_DELETE_SAMPLE_INFO);
				ps2.setLong(1, order.getOrderId());
				ps2.execute();
				for (OrderBean.SampleInfo sampleInfo : sampleInfoList) {
					for (Long sectionId : sampleInfo.getFieldListMap().keySet()) {
						int recordOrder = 0;
						List<List<ModelDetail.Field>> fields = sampleInfo
								.getFieldListMap().get(sectionId);
						for (List<ModelDetail.Field> fieldList : fields) {
							recordOrder++;
							for (ModelDetail.Field field : fieldList) {
								int idx = 1;
								ps3.setLong(idx++, order.getOrderId());
								ps3.setString(idx++, sampleInfo
										.getClientUniqueId());
								ps3.setLong(idx++, sectionId);
								ps3.setLong(idx++, field.getFieldId());
								ps3.setString(idx++, field.getSelectedValue());
								ps3.setInt(idx++, recordOrder);
								ps3.execute();
							}
						}
					}
				}
			}
			conn.commit();
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs2, ps2, ps3);
		}
		log.exit("saveSampleInfo");
	}

	private void saveTestPartInfo(OrderBean order) {
		log.enter("saveTestPartInfo");
		if (order == null) {
			return;
		}
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return;
			}
			ps = conn.prepareStatement(SQL_UPDATE_ORDER_INPUT_STATUS);
			ps.setInt(1, 3);
			ps.setLong(2, order.getOrderId());
			ps.execute();
			conn.commit();
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps);
		}
	}

	public void saveMatrixInfo(OrderBean order) {
		log.enter("saveMatrixInfo");
		if (order == null) {
			return;
		}
		List<OrderBean.MatrixInfo> matrixInfoList = order.getMatrixInfoList();
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs2 = null;
		try {
			if (matrixInfoList != null && matrixInfoList.size() > 0) {
				conn = getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return;
				}
				ps = conn.prepareStatement(SQL_UPDATE_ORDER_INPUT_STATUS);
				ps.setInt(1, 2);
				ps.setLong(2, order.getOrderId());
				ps.execute();
				ps3 = conn.prepareStatement(SQL_INSERT_MATRIX_INFO);
				ps2 = conn.prepareStatement(SQL_DELETE_MATRIX_INFO);
				ps2.setLong(1, order.getOrderId());
				ps2.execute();
				for (OrderBean.MatrixInfo matrixInfo : matrixInfoList) {
					int recordOrder = 0;
					for (List<ModelDetail.Field> fieldList : matrixInfo
							.getFieldList()) {
						recordOrder++;
						for (ModelDetail.Field field : fieldList) {
							int idx = 1;
							ps3.setLong(idx++, order.getOrderId());
							ps3
									.setString(idx++, matrixInfo
											.getClientUniqueId());
							ps3.setLong(idx++, field.getFieldId());
							ps3.setString(idx++, field.getSelectedValue());
							ps3.setInt(idx++, recordOrder);
							ps3.execute();
						}
					}
				}
				conn.commit();
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs2, ps2, ps3);
		}
		log.exit("saveMatrixInfo");
	}

	public List<OrderScheduleInfo> getScheduleData(int year, int month) {
		log.enter("getScheduleData");
		List<OrderScheduleInfo> scheduleList = new ArrayList<OrderScheduleInfo>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return scheduleList;
			}
			ps = conn.prepareStatement(SQL_GET_SCHEDULE_DATA);
			Calendar cal = new GregorianCalendar();
			cal.set(year, month - 1, 1);
			cal.set(Calendar.HOUR_OF_DAY, 0);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			ps.setTimestamp(1, new java.sql.Timestamp(cal.getTime().getTime()));
			cal.add(Calendar.MONTH, 1);
			ps.setTimestamp(2, new java.sql.Timestamp(cal.getTime().getTime()));
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				String orderNumber = rs.getString(i++);
				String modelName = rs.getString(i++);
				String condition = rs.getString(i++);
				String duration = rs.getString(i++);
				java.sql.Timestamp startTime = rs.getTimestamp(i++);
				java.sql.Timestamp endTime = rs.getTimestamp(i++);
				OrderScheduleInfo scheduleInfo = new OrderScheduleInfo();
				scheduleInfo.setRequestNo(orderNumber);
				scheduleInfo.setModelName(modelName);
				scheduleInfo.setCondition(condition);
				scheduleInfo.setDuration(duration);
				scheduleInfo.setStartTime(new java.util.Date(startTime
						.getTime()));
				scheduleInfo.setEndTime(new java.util.Date(endTime.getTime()));
				scheduleList.add(scheduleInfo);
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		log.exit("getScheduleData");
		return scheduleList;
	}

	public void updateScheduleTimeSlot(List<OrderScheduleInfo> scheduleInfoList) {
		log.enter("updateScheduleTimeSlot");
		Connection conn = null;
		PreparedStatement ps = null;
		if (scheduleInfoList != null && !scheduleInfoList.isEmpty()) {
			try {
				conn = getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return;
				}
				ps = conn.prepareStatement(SQL_UPDATE_SCHEDULE_TIMESLOT);
				for (OrderScheduleInfo orderScheduleInfo : scheduleInfoList) {
					ps.setTimestamp(1, new java.sql.Timestamp(orderScheduleInfo
							.getStartTime().getTime()));
					ps.setTimestamp(2, new java.sql.Timestamp(orderScheduleInfo
							.getEndTime().getTime()));
					ps.setString(3, orderScheduleInfo.getClientUniqueId());
					ps.setLong(4, orderScheduleInfo.getOrderId());
					ps.execute();
				}
				conn.commit();
			} catch (SQLException e) {
				log.warn(e);
				try {
					conn.rollback();
				} catch (Exception e1) {
					log.warn(e1);
				}
			} finally {
				close(conn, null, ps);
			}
		}
		log.exit("updateScheduleTimeSlot");
	}

	public boolean saveOrderTemplate(OrderTemplateBean template) {
		log.enter("saveOrderTemplate");
		boolean result = false;
		if (template == null) {
			return result;
		}
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return result;
			}
			int i = 1;
			ps = conn.prepareStatement(SQL_UPDATE_ORDER_TEMPLATE);
			ps.setString(i++, template.getTemplateName());
			ps.setString(i++, template.isPrivate() ? "Y" : "N");
			ps.setLong(i++, template.getTemplateId());
			ps.execute();
			conn.commit();
			result = true;
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (Exception e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps);
		}
		log.enter("saveOrderTemplate");
		return result;
	}

	public boolean deleteOrder(long orderId) {
		log.enter("deleteOrder");
		boolean result = false;
		Connection conn = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		PreparedStatement ps4 = null;
		PreparedStatement ps5 = null;
		PreparedStatement ps6 = null;
		PreparedStatement ps7 = null;
		PreparedStatement ps8 = null;
		PreparedStatement ps9 = null;
		PreparedStatement ps10 = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return result;
			}
			ps1 = conn.prepareStatement(SQL_DELETE_ORDER_HISTORY);
			ps1.setLong(1, orderId);
			ps1.execute();
			ps2 = conn.prepareStatement(SQL_DELETE_ORDER_FIELD_VALUES);
			ps2.setLong(1, orderId);
			ps2.execute();
			ps3 = conn.prepareStatement(SQL_DELETE_ORDER_MODELS);
			ps3.setLong(1, orderId);
			ps3.execute();
			ps4 = conn.prepareStatement(SQL_DELETE_GENERAL_INFO);
			ps4.setLong(1, orderId);
			ps4.execute();
			ps5 = conn.prepareStatement(SQL_DELETE_SAMPLE_INFO);
			ps5.setLong(1, orderId);
			ps5.execute();
			ps6 = conn.prepareStatement(SQL_DELETE_MATRIX_INFO);
			ps6.setLong(1, orderId);
			ps6.execute();
			ps7 = conn.prepareStatement(SQL_DELETE_ORDER);
			ps7.setLong(1, orderId);
			ps7.execute();
			ps8 = conn.prepareStatement(SQL_DELETE_SCHEDULE_INFO);
			ps8.setLong(1, orderId);
			ps8.execute();
			ps9 = conn.prepareStatement(SQL_DELETE_ORDER_TEMPLATE);
			ps9.setLong(1, orderId);
			ps9.execute();
			ps10 = conn.prepareStatement(SQL_DELETE_REF_USER);
			ps10.setLong(1, orderId);
			ps10.execute();
			conn.commit();
			result = true;
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (Exception e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps1, ps2, ps3, ps4, ps5, ps6, ps7, ps8, ps9, ps10);
		}
		log.exit("deleteOrder", result);
		return result;
	}

	public void autoCompleteOrder() {
		log.enter("autoCompleteOrder");
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return;
			}
			ps = conn.prepareStatement(SQL_COMPLTE_ORDERS);
			ps.execute();
			conn.commit();
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (Exception e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps);
		}
		log.exit("autoCompleteOrder");
	}

	public List<OrderTemplateBean> getOrderTemplates(long userId, int category) {
		List<OrderTemplateBean> templateList = new ArrayList<OrderTemplateBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return templateList;
			}
			ps = conn.prepareStatement(SQL_GET_ORDER_TEMPLATES);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				OrderTemplateBean template = new OrderTemplateBean();
				template.setTemplateId(rs.getLong(i++));
				template.setOrderId(rs.getLong(i++));
				template.setTemplateName(rs.getString(i++));
				template.setPrivate("Y".equalsIgnoreCase(rs.getString(i++)));
				if (category == OrderTemplateBean.CATEGORY_ALL) {
					long createUserId = rs.getLong(i++);
					// when get all kinds of template, only return the ones the
					// specified user created
					if (userId == createUserId) {
						templateList.add(template);
					}
				} else if (category == OrderTemplateBean.CATEGORY_PRIVATE
						&& template.isPrivate()) {
					templateList.add(template);
				} else if (category == OrderTemplateBean.CATEGORY_PUBLIC
						&& !template.isPrivate()) {
					templateList.add(template);
				}
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		return templateList;
	}

	public long checkOrderTemplate(String templateName, long userId) {
		long templateId = -1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return templateId;
			}
			ps = conn.prepareStatement(SQL_GET_ORDER_TEMPLATE);
			ps.setString(1, templateName);
			ps.setString(2, templateName);
			ps.setLong(3, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				templateId = rs.getLong(1);
			}
		} catch (SQLException e) {
			log.warn(e);
		} finally {
			close(conn, rs, ps);
		}
		return templateId;
	}

	public boolean saveOrderTemplate(String templateName, long orderId,
			boolean isPublic, long userId) {
		boolean result = false;
		OrderBean order = getOrder(orderId);
		if (order != null) {
			order.setOrderId(-1);
			order.getGeneralInfo().setId(-1);
			order.setStatus(0);
			long newOrderId = saveOrder(order, 1, true);
			order.setOrderId(newOrderId);
			// saveOrder(order, 2);
			// saveOrder(order, 3);
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				conn = getConnection();
				if (conn == null) {
					log.debug("can't get db connection.");
					return result;
				}
				ps = conn.prepareStatement(SQL_SAVE_ORDER_TEMPLATE);
				ps.setString(1, templateName);
				ps.setString(2, isPublic ? "N" : "Y");
				ps.setLong(3, newOrderId);
				ps.setLong(4, userId);
				ps.execute();
				conn.commit();
				result = true;
			} catch (SQLException e) {
				log.warn(e);
				try {
					conn.rollback();
				} catch (Exception e1) {
					log.warn(e1);
				}
			} finally {
				close(conn, null, ps);
			}
		}
		return result;
	}

	private String getPrjName(String project, String product) {
		StringBuilder sb = new StringBuilder();
		sb.append(StringUtils.trimToEmpty(project)).append(" ").append(
				StringUtils.trimToEmpty(product));
		return sb.toString();
	}

	public boolean transferOrder(OrderBean order, long newTeUserId,
			long actorUserId, String sampleArr, String fixtureArr,
			String comment) {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		boolean ret = false;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return ret;
			}
			// update new test user to general info table
			ps = conn.prepareStatement(SQL_TRANSFER_ORDER);
			ps.setLong(1, newTeUserId);
			ps.setLong(2, order.getOrderId());
			ps.execute();

			// update the order_ref_user table
			ps3 = conn.prepareStatement(SQL_ADD_REF_USER);
			ps3.setLong(1, order.getOrderId());
			ps3.setLong(2, order.getGeneralInfo().getTestUser().getUserId());
			ps3.execute();

			// update the sample&fixture in schedule table for each model
			String[] samples = null;
			String[] fixtures = null;
			List<ModelBean> modelList = null;
			if (sampleArr != null) {
				samples = sampleArr.split(TRPConstants.COMMA);
			}
			if (fixtureArr != null) {
				fixtures = fixtureArr.split(TRPConstants.COMMA);
			}
			if (order.getModelMap() != null) {
				modelList = order.getModelMap().get(1);
			}
			if (samples != null && fixtures != null && modelList != null
					&& samples.length >= modelList.size()) {
				ps2 = conn.prepareStatement(SQL_UPDATE_SCHEDULE_SAMPLE_FIXTURE);
				for (int i = 0; i < modelList.size(); i++) {
					ps2.setString(1, samples[i]);
					ps2.setString(2, fixtures[i]);
					ps2.setString(3, modelList.get(i).getClientUniqueId());
					ps2.setLong(4, order.getOrderId());
					ps2.execute();
				}
			}

			createOrderHistory(conn, ORDER_ACTION.Transfer.toString(), order
					.getOrderId(), actorUserId, comment);
			conn.commit();
			ret = true;
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (Exception e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps, ps2, ps3);
		}
		return ret;
	}

	public void refreshScheduleInfo(OrderBean order) {
		refreshScheduleInfo(order, null);
	}

	private void refreshScheduleInfo(OrderBean order, Connection conn) {
		boolean needReleaseConnection = false;
		try {
			if (conn == null) {
				conn = getConnection();
				needReleaseConnection = true;
			}
			if (conn == null) {
				log.debug("can't get db connection.");
				return;
			}
			Map<String, OrderScheduleInfo> scheduleInfo = getScheduleInfoMap(
					conn, order);
			Set<String> removeList = new HashSet<String>();
			Set<String> newList = new HashSet<String>();
			Set<String> newAddList = new HashSet<String>();
			if (order != null && order.getModelMap() != null) {
				List<ModelBean> modelList = order.getModelMap().get(1);
				if (modelList != null) {
					for (ModelBean model : modelList) {
						if (scheduleInfo.get(model.getClientUniqueId()) == null) {
							newAddList.add(model.getClientUniqueId());
						}
						newList.add(model.getClientUniqueId());
					}
				}
			}
			removeList = scheduleInfo.keySet();
			removeList.removeAll(newList);
			deleteScheduleInfo(conn, order.getOrderId(), removeList);
			createScheduleInfo0(order.getOrderId(), newAddList, conn);
			if (needReleaseConnection) {
				conn.commit();
			}
		} catch (Exception e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			if (needReleaseConnection) {
				close(conn, null);
			}
		}
	}

	private void deleteScheduleInfo(Connection conn, long orderId,
			Collection<String> cuidList) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(SQL_DELETE_SCHEDULE_INFO2);
			for (String cuid : cuidList) {
				ps.setLong(1, orderId);
				ps.setString(2, cuid);
				ps.executeUpdate();
			}
		} catch (Exception e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(null, null, ps);
		}
	}

	public void updateScheduleStatus(long orderId, String clientUniqueId,
			int newStatus) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return;
			}
			OrderBean order = getOrder(orderId, conn);
			Map<String, OrderScheduleInfo> scheduleInfoMap = order
					.getScheduleInfoMap();
			// deal with the old request here
			List<ModelBean> modelList = order.getModelMap().get(1);
			if (modelList.size() != scheduleInfoMap.size()) {
				refreshScheduleInfo(order, conn);
			}
			scheduleInfoMap = getScheduleInfoMap(conn, order);
			order.setScheduleInfoMap(scheduleInfoMap);

			// update schedule info status
			ps = conn.prepareStatement(SQL_UPDATE_SCHEDULE_STATUS);
			ps.setInt(1, newStatus);
			ps.setString(2, clientUniqueId);
			ps.setLong(3, orderId);
			ps.execute();
			scheduleInfoMap.get(clientUniqueId).setStatus(newStatus);

			// update order status accordingly
			int status = order.getStatus();
			boolean otherStatus = false;
			for (ModelBean model : modelList) {
				OrderScheduleInfo scheduleInfo = scheduleInfoMap.get(model
						.getClientUniqueId());
				if (scheduleInfo.getStatusName() != null) {
					// ignore cancel status
					if (scheduleInfo.getStatus() != -3) {
						status = Math.min(status, scheduleInfo.getStatus());
						otherStatus = true;
					}
				} else {
					otherStatus = true;
				}
			}
			if (status > -999) {
				changeOrderStatus(conn, order, otherStatus ? status : -3, null,
						null, null);
			}
			conn.commit();
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (Exception e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps);
		}

	}

}
